﻿CREATE PROCEDURE [dbo].[FinancialLeaseContractDetailReport]
	@FromDate datetime,
	@EndDate datetime,
	@ProjectId int
WITH RECOMPILE 
AS
BEGIN
	SET @EndDate = DateAdd(day, 1, @EndDate);
	WITH 
	[EntranceView]
	AS
	(
		SELECT	[Id],
						[BuildingId], 
						[Name]
		FROM		[dbo].[Entrance]
		WHERE	[IsDeleted] = 0
	),
	[FloorView]
	AS
	(
		SELECT	[Id],
						[BuildingId], 
						[Name]
		FROM		[dbo].[Floor]
		WHERE	[IsDeleted] = 0
	),
	[BuildingView]
	AS
	(
		SELECT	[Id],
						[ProjectId], 
						[Name]
		FROM		[dbo].[Building]
		WHERE	[IsDeleted] = 0
	),
	[ProjectView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[Project]
		WHERE	[IsDeleted] = 0 AND [Id] = @ProjectId
	),
	[EmployeeView]
	AS
	(
		SELECT	[Id],
						[Name]
		FROM		[dbo].[Employee]
		WHERE	[IsDeleted] = 0 AND [ProjectId] = @ProjectId
	),
	[CustomerView]
	AS
	(
		SELECT	[Id],
						[Name],
						[Address], 
						[IdentityNumber], 
						[Mobile]
		FROM		[dbo].[Customer]
		WHERE	[IsDeleted] = 0 AND [ProjectId] = @ProjectId
	),
	[LeasePaymentTypeView]
	AS
	(
		SELECT	[Id], 
						[Name]
		FROM		[dbo].[LeasePaymentType]
		WHERE	[IsDeleted] = 0
	),
	[LeaseAgreementView]
	AS
	(
		SELECT	[Id],
						[PropertyId],
						[CustomerId],
						[SalesEmployeeId]
		FROM		[dbo].[LeaseAgreement]
		WHERE	[IsDeleted] = 0
	),
	[LeaseOrderView]
	AS
	(
		SELECT	[Id], 
						[LeaseAgreementId],
						[DepositAmount], 
						[SignatureDate]
		FROM		[dbo].[LeaseOrder]
		WHERE	[IsDeleted] = 0 AND [SignatureDate] < @EndDate
	),
	[LeaseContractReceiptView]
	AS
	(
			SELECT	[Id], 
							[LeaseContractId], 
							[ReceiptTypeId], 
							[ScheduledDate], 
							[ReceivableAmount], 
							[ReceivedAmount], 
							ROW_NUMBER() OVER (PARTITION BY [LeaseContractId] ORDER BY [ScheduledDate] ASC) AS [RowNumber]
			FROM		[dbo].[LeaseContractReceipt]
			WHERE	[IsDeleted] = 0 AND [ScheduledDate] < @EndDate
	),
	[LeaseContractView]
	AS
	(
		SELECT	C.[Id], 
						C.[LeaseAgreementId],
						C.[ContractNumber], 
						C.[ReducedUnitPricePerMonth], 
						C.[ReducedTotalPricePerMonth], 
						C.[LeaseMonths], 
						PT.[Name] AS [LeasePaymentType], 
						C.[SignatureDate], 
						C.[TotalAmount], 
						C.[LeaseAmount], 
						C.[GuarantyAmount], 
						ISNULL(CR.[ReceivableAmount], 0) AS [ReceivableAmount], 
						ISNULL(CR.[ReceivedAmount], 0) AS [ReceivedAmount], 
						CASE WHEN ISNULL(CR.[ReceivableAmount], 0) = ISNULL(CR.[ReceivedAmount], 0) THEN 1 ELSE 0 END AS [Received], 
						C.[Comment]
		FROM		[dbo].[LeaseContract] AS C
			INNER JOIN [LeasePaymentTypeView] AS PT ON C.[LeasePaymentTypeId] = PT.[Id] 
			LEFT OUTER JOIN
			(
				SELECT	[LeaseContractId],
								SUM([ReceivableAmount]) AS [ReceivableAmount],
								SUM([ReceivedAmount]) AS [ReceivedAmount]
				FROM		[LeaseContractReceiptView]
				GROUP BY	
								[LeaseContractId]
			) AS CR
			ON C.[Id] = CR.[LeaseContractId]
		WHERE	C.[IsDeleted] = 0 AND C.[SignatureDate] >= @FromDate AND C.[SignatureDate] < @EndDate
	)
	SELECT	P.[Id] AS [ProjectId], 
					B.[Id] AS [BuildingId],
					E.[Id] AS [EntranceId],
					F.[Id] AS [FloorId],
					R.[Id] AS [PropertyId],
					R.[Name] AS [PropertyName], 
					R.[LeaseArea],
					AG.[Id] AS [LeaseAgreementId], 
					CT.[Id] AS [CustomerId], 
					CT.[Name] AS [CustomerName], 
					CT.[Address] AS [CustomerAddress], 
					CT.[IdentityNumber] AS [CustomerIdentityNumber], 
					CT.[Mobile] AS [CustomerMobile], 
					EP.[Id] AS [EmployeeId], 
					EP.[Name] AS [EmployeeName], 
					LO.[Id] AS [LeaseOrderId], 
					LO.[SignatureDate] AS [LeaseOrderSignatureDate], 
					LO.[DepositAmount] AS [LeaseOrderDepositAmount], 
					C.[Id] AS [LeaseContractId], 
					C.[ContractNumber] AS [LeaseContractNumber], 
					C.[SignatureDate] AS [LeaseContractSignatureDate], 
					C.[ReducedUnitPricePerMonth] AS [LeaseContractUnitPricePerMonth], 
					C.[ReducedTotalPricePerMonth] AS [LeaseContractTotalPricePerMonth], 
					C.[LeaseMonths], 
					C.[LeasePaymentType], 
					C.[TotalAmount], 
					C.[LeaseAmount], 
					C.[GuarantyAmount], 
					C.[ReceivableAmount], 
					C.[ReceivedAmount], 
					C.[Received], 
					C.[Comment]
	FROM		[dbo].[Property] AS R 
		INNER JOIN [EntranceView] AS E ON R.[EntranceId] = E.[Id]
		INNER JOIN [FloorView] AS F ON R.[FloorId] = F.[Id]
		INNER JOIN [BuildingView] AS B ON E.[BuildingId] = B.[Id]
		INNER JOIN [ProjectView] AS P ON B.[ProjectId] = P.[Id]
		INNER JOIN [LeaseAgreementView] AS AG ON R.[ActiveLeaseAgreementId] = AG.[Id]
		INNER JOIN [CustomerView] AS CT ON AG.[CustomerId] = CT.[Id] 
		INNER JOIN [EmployeeView] AS EP ON AG.[SalesEmployeeId] = EP.[Id] 
		LEFT OUTER JOIN [LeaseOrderView] AS LO ON AG.[Id] = LO.[LeaseAgreementId]
		LEFT OUTER JOIN [LeaseContractView] AS C ON AG.[Id] = C.[LeaseAgreementId]
	WHERE R.[IsDeleted] = 0 AND R.[PropertyStateId] = 4
END
